--Testdaten Version 1.0

COMMIT;

SET NAMES 'UTF8';

drop table if exists hibernate_sequence;
drop table if exists TimeInterval;
drop table if exists Employee;
drop table if exists Activity;
drop table if exists Department;



create table Activity (id bigint not null, description varchar(255) not null, primary key (id));
create table Department (id bigint not null, description varchar(255) not null, primary key (id));
create table Employee (id bigint not null, employmentType float not null, firstName varchar(255), isDepartmentManager bit, lastName varchar(255), password varchar(255) not null, staffNumber bigint not null, username varchar(255) not null, department_id bigint, primary key (id));
create table TimeInterval (id bigint not null, comment varchar(255), from_Date datetime not null, to_Date datetime not null, activity_id bigint, employee_id bigint, primary key (id));
alter table Employee add constraint UK_j2ftt6g7a8ohsf079225ldlf9  unique (staffNumber);
alter table Employee add constraint UK_v9p52k4owkd6pgqpiw3r2ovl  unique (username);
alter table Employee add constraint FK_lk0a412kck2kdc6slousi528s foreign key (department_id) references Department (id);
alter table TimeInterval add constraint FK_c9inj7a6d26yje7uuvwubp811 foreign key (activity_id) references Activity (id);
alter table TimeInterval add constraint FK_mg8s4e2y09x6wefxk806yupsg foreign key (employee_id) references Employee (id);
create table hibernate_sequence ( next_val bigint );
insert into hibernate_sequence values ( 1 );


BEGIN;

--Department hinzufügen
INSERT INTO Department VALUES(1,'Internettechnik');

 
--Activity erstellen
INSERT INTO Activity VALUES(1,'Projektarbeit');


--Employee erstellen   Frage Employment type float??

INSERT INTO Employee VALUES (1,0.5,'Josef',0,'Heinel',MD5('josef'),131112555,'jheinel',1);


COMMIT;


--Urlaub - 8 Stunden
--INSERT INTO TimeInterval VALUES (5,'Urlaub',2014-11-24 08:00:00,2014-11-24 16:30:00,2,2);
--Krankenstand - 8 Stunden
--INSERT INTO TimeInterval VALUES (6,'Krankenstand',2014-11-24 08:00:00,2014-11-24 16:30:00,1,4);
--Feiertag - 0 Stunden
--INSERT INTO TimeInterval VALUES (6,'Feiertag',2014-11-24 08:00:00,2014-11-24 11:30:00,3,5);
